﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;

namespace SWEN3Application.Classes
{
    public class HousekeepingDBManager
    {
        public static ArrayList GetAllHousekeeping()
        {
            ArrayList result = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Housekeeping";
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                while (dr.Read())
                {
                    housekeepingOscar h = new housekeepingOscar();

                    h.roomNo = (int)dr["RoomNo"];
                    h.roomStatus = (String)dr["RoomStatus"];
                    h.roomType = (String)dr["RoomType"];
                    h.cleaningStartTime = (DateTime)(dr["CleaningStartTime"] != DBNull.Value ? dr["CleaningStartTime"] : Default);
                    h.cleaningEndTime = (DateTime)(dr["CleaningEndTime"] != DBNull.Value ? dr["CleaningEndTime"] : Default);
                    h.inCharge = (String)dr["InCharge"];
                    result.Add(h);


                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public static int InsertHousekeeping(housekeepingOscar h)
        {
            int rowsinserted = 0;
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Housekeeping(RoomNo,InCharge,CleaningStartTime,CleaningEndTime,RoomStatus,RoomType)"
                + " VALUES (@RoomNo,@InCharge,@CleaningStartTime,@CleaningEndTime,@RoomStatus,@RoomType)";
                comm.Parameters.AddWithValue("@RoomNo", h.roomNo);
                comm.Parameters.AddWithValue("@InCharge", h.inCharge);
                comm.Parameters.AddWithValue("@CleaningStartTime", h.cleaningStartTime);
                comm.Parameters.AddWithValue("@CleaningEndTime", h.cleaningEndTime);
                comm.Parameters.AddWithValue("@RoomStatus", h.roomStatus);
                comm.Parameters.AddWithValue("@RoomType", h.roomType);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return rowsinserted;
        }

    
        
        public static ArrayList GetAllRooms()
        {

            ArrayList Room = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT RoomNo FROM Housekeeping";
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeepingOscar h = new housekeepingOscar();

                    h.roomNo = (int)dr["RoomNo"];
                    Room.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch(SqlCeException e)
            {
                throw e;
            }
            return Room;
        }

        public static housekeepingOscar GetRoomByRoomNo(int RoomNo)
        {
            housekeepingOscar h = null;
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Housekeeping WHERE RoomNo=@RoomNo";
                comm.Parameters.AddWithValue("@RoomNo", RoomNo);
                SqlCeDataReader dr = comm.ExecuteReader();
                if(dr.Read())
                {
                    h = new housekeepingOscar();
                    h.roomNo = (int)dr["RoomNo"];
                    h.inCharge = (string)dr["InCharge"];
                    h.cleaningStartTime = (DateTime)dr["CleaningStartTime"];
                    h.cleaningEndTime = (DateTime)dr["CleaningEndTime"];
                    h.roomStatus = (string)dr["RoomStatus"];
                    h.roomType = (string)dr["RoomType"];
                }
                    dr.Close();
                    conn.Close();
            }
                catch(SqlCeException e)
                {
                   throw e;
                }
                return h;
        }

        public static ArrayList GetAllStatus(string RoomStatus)
        {
            ArrayList Status = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
               conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Housekeeping WHERE RoomStatus=@RoomStatus";
                comm.Parameters.AddWithValue("@RoomStatus", RoomStatus);
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeepingOscar h = new housekeepingOscar();
                    h.roomNo = (int)dr["RoomNo"];
                    h.inCharge = (string)dr["InCharge"];
                    h.cleaningStartTime = (DateTime)dr["CleaningStartTime"];
                    h.cleaningEndTime = (DateTime)dr["CleaningEndTime"];
                    h.roomStatus = (string)dr["RoomStatus"];
                    h.roomType = (string)dr["RoomType"];
                    Status.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return Status;
        }
        public static ArrayList GetAllStatusCombo()
        {
            ArrayList Status = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Housekeeping";
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeepingOscar h = new housekeepingOscar();
                    h.roomNo = (int)dr["RoomNo"];
                    h.inCharge = (string)dr["InCharge"];
                    h.cleaningStartTime = (DateTime)dr["CleaningStartTime"];
                    h.cleaningEndTime = (DateTime)dr["CleaningEndTime"];
                    h.roomStatus = (string)dr["RoomStatus"];
                    h.roomType = (string)dr["RoomType"];
                    Status.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return Status;
        }
        public static ArrayList GetAllRoomNo(int roomNo)
        {
            ArrayList Status = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Housekeeping WHERE RoomNo=@RoomNo";
                comm.Parameters.AddWithValue("@RoomNo", roomNo);
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeepingOscar h = new housekeepingOscar();
                    h.roomNo = (int)dr["RoomNo"];
                    h.inCharge = (string)dr["InCharge"];
                    h.cleaningStartTime = (DateTime)dr["CleaningStartTime"];
                    h.cleaningEndTime = (DateTime)dr["CleaningEndTime"];
                    h.roomStatus = (string)dr["RoomStatus"];
                    h.roomType = (string)dr["RoomType"];
                    Status.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return Status;
        }
        public static ArrayList GetAllRoom()
        {
            ArrayList Status = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Housekeeping";
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeepingOscar h = new housekeepingOscar();
                    h.roomNo = (int)dr["RoomNo"];
                    h.inCharge = (string)dr["InCharge"];
                    h.cleaningStartTime = (DateTime)dr["CleaningStartTime"];
                    h.cleaningEndTime = (DateTime)dr["CleaningEndTime"];
                    h.roomStatus = (string)dr["RoomStatus"];
                    h.roomType = (string)dr["RoomType"];
                    Status.Add(h);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return Status;
        }
        public static housekeepingOscar GetByDate(DateTime cleaningStartTime, DateTime cleaningEndTime)//DateTime CheckIn2
        {
            SqlCeConnection conn = null;
            housekeepingOscar h = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Housekeeping WHERE CleaningStartTime>= @CleaningStartTime and CleaningEndTime <= @CleaningEndTime";
                comm.Parameters.AddWithValue("@CleaningStartTime", cleaningStartTime);
                //comm.Parameters.AddWithValue("@CheckIn", CheckIn2);
                comm.Parameters.AddWithValue("@CleaningEndTime", cleaningEndTime);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                //RoomStatus = 'Occupied' and  and CheckOut <= @ CheckOut"
                if (dr.Read())
                {
                    h = new housekeepingOscar();
                    h.roomNo = (int)dr["RoomNo"];
                    h.inCharge = (string)dr["InCharge"];
                    h.cleaningStartTime = (DateTime)dr["CleaningStartTime"];
                    h.cleaningEndTime = (DateTime)dr["CleaningEndTime"];
                    h.roomStatus = (string)dr["RoomStatus"];
                    h.roomType = (string)dr["RoomType"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return h;
        }

        public static int UpdateHousekeeping(housekeepingOscar u)
        {
            int rowsupdated = 0;

            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Housekeeping SET  RoomType=@RoomType, RoomStatus=@RoomStatus, CleaningStartTime=@CleaningStartTime, CleaningEndTime=@CleaningEndTime, InCharge=@InCharge WHERE RoomNo=@RoomNo";
                comm.Parameters.AddWithValue("@RoomNo", u.roomNo);
                comm.Parameters.AddWithValue("@InCharge", u.inCharge);
                comm.Parameters.AddWithValue("@RoomType", u.roomType);
                comm.Parameters.AddWithValue("@RoomStatus", u.roomStatus);
                comm.Parameters.AddWithValue("@CleaningStartTime", u.cleaningStartTime);
                comm.Parameters.AddWithValue("@CleaningEndTime", u.cleaningEndTime);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return rowsupdated;
        }

    }
    }

